package util.office;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import util.toolkit.TimeUtil;
import model.TTeacherInfo;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class NewReadExcel {

	/**
	 * 去读Excel的方法readExcel，该方法的入口参数为一个File对象，绝对路径读取
	 * @param file Excel的路径
	 * @return
	 */
	public static List readExcel(File file) {
		try {
			// 创建输入流，读取Excel
			InputStream is = new FileInputStream(file.getAbsolutePath());
			// jxl提供的Workbook类
			Workbook wb = Workbook.getWorkbook(is);
			// Excel的页签数量
			int sheet_size = wb.getNumberOfSheets();
			for (int index = 0; index < sheet_size; index++) {
				List<List> outerList = new ArrayList<List>();
				// 每个页签创建一个Sheet对象
				Sheet sheet = wb.getSheet(index);
				//System.out.println(sheet.getRows());//返回该页的总行数
				for (int i = 1; i < sheet.getRows(); i++) {
					List innerList = new ArrayList();
				//	System.out.println(sheet.getColumns());//返回该页的总列数
					for (int j = 0; j < sheet.getColumns(); j++) {
						String cellinfo = sheet.getCell(j, i).getContents();
						if (cellinfo.isEmpty()) {
							innerList.add("无");
							continue;
						}
						innerList.add(cellinfo);
					}
					outerList.add((i - 1), innerList);
				}
				return outerList;
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 去读Excel的方法readExcel，文件上传读取调用
	 * @param request
	 * @param response
	 * @return
	 */
	public static List readExcel(HttpServletRequest request, HttpServletResponse response) {
		try {
			// 解析 创建上传文件对象
			ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());
			List itemList = upload.parseRequest(request);
			for (Iterator it = itemList.iterator(); it.hasNext();) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField()){// 如果是非表单数据（没有放在表单里）
					//获取输入流，读取Excel
					InputStream is = item.getInputStream();
					// jxl提供的Workbook类
					Workbook wb = Workbook.getWorkbook(is);
					// Excel的页签数量
					int sheet_size = wb.getNumberOfSheets();
					for (int index = 0; index < sheet_size; index++) {
						List<List> outerList = new ArrayList<List>();
						// 每个页签创建一个Sheet对象
						Sheet sheet = wb.getSheet(index);
						//System.out.println(sheet.getRows());//返回该页的总行数
						for (int i = 1; i < sheet.getRows(); i++) {
							List innerList = new ArrayList();
						//	System.out.println(sheet.getColumns());//返回该页的总列数
							for (int j = 0; j < sheet.getColumns(); j++) {
								String cellinfo = sheet.getCell(j, i).getContents();
								if (cellinfo.isEmpty()) {
									innerList.add("无");
									continue;
								}
								innerList.add(cellinfo);
							}
							outerList.add((i - 1), innerList);
						}
						return outerList;
					}
				}
			}			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 去读Excel的方法readExcel，文件上传读取调用（多一行说明的调用）
	 * @param request
	 * @param response
	 * @return
	 */
	public static List readExcel2(HttpServletRequest request, HttpServletResponse response) {
		try {
			// 解析 创建上传文件对象
			ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());
			List itemList = upload.parseRequest(request);
			for (Iterator it = itemList.iterator(); it.hasNext();) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField()){// 如果是非表单数据（没有放在表单里）
					//获取输入流，读取Excel
					InputStream is = item.getInputStream();
					// jxl提供的Workbook类
					Workbook wb = Workbook.getWorkbook(is);
					// Excel的页签数量
					int sheet_size = wb.getNumberOfSheets();
					for (int index = 0; index < sheet_size; index++) {
						List<List> outerList = new ArrayList<List>();
						// 每个页签创建一个Sheet对象
						Sheet sheet = wb.getSheet(index);
						//System.out.println(sheet.getRows());//返回该页的总行数
						for (int i = 2; i < sheet.getRows(); i++) {
							List innerList = new ArrayList();
						//	System.out.println(sheet.getColumns());//返回该页的总列数
							for (int j = 0; j < sheet.getColumns(); j++) {
								String cellinfo = sheet.getCell(j, i).getContents();
								if (cellinfo.isEmpty()) {
									innerList.add("无");
									continue;
								}
								innerList.add(cellinfo);
							}
							outerList.add((i - 2), innerList);
						}
						return outerList;
					}
				}
			}			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void main(String[] args) {
		// 此处为Excel的路径
		File file = new File("C:\\Users\\Administrator\\Desktop\\教师信息表.xls");
		List excelList = readExcel(file);
		List<String> outerList = null;
		List<TTeacherInfo> teaList = new ArrayList<TTeacherInfo>();
		TimeUtil tu = new TimeUtil();
		
		try {
			for (int i = 0; i < excelList.size(); i++) {
				outerList = (List<String>) excelList.get(i);
				TTeacherInfo teacherInfo = new TTeacherInfo();
				for(int j = 0; j < outerList.size(); j++){					
					switch (j) {
					case 0:
						teacherInfo.setRealName(outerList.get(j).toString());
						break;
					case 1:
						teacherInfo.setSex(outerList.get(j).toString());
						break;
					case 2:
						teacherInfo.setBirthday(tu.formatConversion(outerList.get(j).toString()));
						break;
					case 3:
						teacherInfo.setEmail(outerList.get(j).toString());
						break;
					case 4:
						teacherInfo.setPhone(outerList.get(j).toString());
						break;
					case 5:
						teacherInfo.setTell(outerList.get(j).toString());
						break;
					case 6:
						teacherInfo.setWorkTel(outerList.get(j).toString());
						break;
					case 7:
						teacherInfo.setAddress(outerList.get(j).toString());
						break;
					case 8:
						teacherInfo.setPost(outerList.get(j).toString());
						break;
					case 9:
						teacherInfo.setGraduateSchool(outerList.get(j).toString());
						break;
					case 10:
						teacherInfo.setEntryTime(tu.formatConversion(outerList.get(j).toString()));
						break;
					case 11:
						teacherInfo.setRoleid(Integer.parseInt(outerList.get(j).toString()));
						break;
					case 12:
						teacherInfo.setSchoolid(outerList.get(j).toString());
						break;
					}
				}
				teaList.add(teacherInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		for (TTeacherInfo tTeacherInfo : teaList) {
			System.out.println(tTeacherInfo.getRealName());
		}

	}

}
